package db.finance;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import db.Database;
import dbservice.finance.AccountorDBService;
import po.AccountBookPo;
import po.AccountorPo;
import po.Po;

public class AccountorDB implements AccountorDBService{
	private AccountBookPo ab;
	
	public AccountorDB(AccountBookPo ab) throws SQLException {
		String creatTable="CREATE TABLE IF NOT EXISTS `seac2`.`"+ab.getName()+"_account` ( `name` VARCHAR(45) NOT NULL, `money` BIGINT(0) NOT NULL,PRIMARY KEY (`name`));";
		Connection c = Database.getConnection();
		Statement s = c.createStatement();
		s.execute(creatTable);
		s.close();
		this.ab=ab;
	}
	
	@Override
	public List<AccountorPo> getAll() {
		List<AccountorPo> list=new ArrayList<AccountorPo>();
		Connection c=Database.getConnection();
		try {			
			Statement s = c.createStatement();
			ResultSet r=s.executeQuery("select * from "+ab.getName()+"_account;");
			while(r.next()){
				AccountorPo po=new AccountorPo();
				po.set名称(r.getString(1));
				po.set金额(r.getLong(2));
				po.successed();
				list.add(po);
			}
			s.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return list;
	}
	
	/**
	 * 向账户列表添加账户
	 * @param accountor
	 * 需要添加的账户的详细信息
	 * @return
	 * 是否添加成功
	 */
	public Po add(AccountorPo accountor){
		Po po=new Po();
		Connection c=Database.getConnection();
		Statement s;
		try {
			s = c.createStatement();
			s.executeUpdate("insert into "+ab.getName()+"_account values('"+accountor.get名称()+"','"+accountor.get金额()+"');");
			s.close();
			po.successed();
		} catch (SQLException e) {
			po.failed(e.getMessage());
			e.printStackTrace();
		}
		return po;
	}

	/**
	 * 删除含有相同名称的账户
	 * @param keyword
	 */
	public Po delete(AccountorPo accountor){
		Po po=new Po();
		Connection c=Database.getConnection();
		Statement s;
		try {
			s = c.createStatement();
			if(s.executeUpdate("delete from "+ab.getName()+"_account where name='"+accountor.get名称()+"';")==0){
				po.failed("账户不存在");
			}else{
				po.successed();
			}
			s.close();
		} catch (SQLException e) {
			po.failed(e.getMessage());
			e.printStackTrace();
		}		
		return po;
	}

	/**
	 * 更新同名称的账户信息
	 * @param Accountor
	 * 需要更新的账户的详细信息
	 * @return	
	 * 是否更新成功
	 */

	public Po update(AccountorPo accountor){
		Po po=new Po();		
		Connection c=Database.getConnection();
		Statement s;
		try {
			if(accountor.get名称()==null){
				throw new NullPointerException();
			}
			s = c.createStatement();
				s.addBatch(("update user set money='"+accountor.get金额()+"' where name='"+accountor.get名称()+"';"));			
			s.executeBatch();
			s.close();
			po.successed();
		} catch (SQLException e) {
			po.failed(e.getMessage());
			e.printStackTrace();
		}		
		return po;
	}

	@Override
	public Po rename(AccountorPo oldpo, AccountorPo newpo) {
		Po po=new Po();		
		Connection c=Database.getConnection();
		Statement s;
		try {
			if(oldpo.get名称()==null){
				throw new NullPointerException();
			}
			s=c.createStatement();
			ResultSet r=s.executeQuery("select * from "+ab.getName()+"_account where name='"+oldpo.get名称()+"';");
			if(r.next()){
				newpo.set金额(r.getLong(2));
				c.setAutoCommit(false);
				s = c.createStatement();
				s.executeUpdate("delete from "+ab.getName()+"_account where name='"+oldpo.get名称()+"';");			
				s.executeUpdate("insert into "+ab.getName()+"_account values('"+newpo.get名称()+"','"+newpo.get金额()+"');");
				c.commit();
				c.setAutoCommit(true);
				po.successed();
			}else{
				po.failed("账户不存在");
			}
			
			
		} catch (SQLException e) {
			po.failed(e.getMessage());
			e.printStackTrace();
		}finally{
			try {
				c.setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return po;
	}

	
}
